"""
Case Type   : CLEAN CONNECTION
Case Name   : 权限验证，其他用户进行清理，合理报错
Create At   : 2022-05-10
Owner       : liu-tong-8848
Description :
    1、创建数据库
    2、创建用户
    3、线程1执行清理操作
    4、线程2执行清理操作
    5、线程3执行清理操作
    6、清理环境
Expect      :
    1、创建数据库，expect: 成功
    2、创建用户，expect: 成功
    3、线程1执行清理操作，expect: 失败
    4、线程2执行清理操作，expect: 失败
    5、线程3执行清理操作，expect: 失败
    6、清理环境，expect: 成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.ComThread import ComThread
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CleanConnection(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'd_clean_0027'
        self.u_name1 = 'u_clean_0027'
        self.u_name2 = 'u_clean_0027_1'
        self.u_name3 = 'u_clean_0027_2'
        self.flag = 'ERROR:  must be system admin to clean pool connections'

    def test_main(self):
        step_txt = '----step1: 创建数据库，expect: 成功----'
        self.log.info(step_txt)
        create_sql = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertTrue(
            create_result.count(self.constant.CREATE_DATABASE_SUCCESS) == 1,
            '执行失败:' + step_txt)

        step_txt = '----step2: 创建用户，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop user if exists {self.u_name1} cascade;" \
            f"create user {self.u_name1} createrole " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name2} cascade;" \
            f"create user {self.u_name2} auditadmin " \
            f"password '{macro.COMMON_PASSWD}';" \
            f"drop user if exists {self.u_name3} cascade;" \
            f"create user {self.u_name3} " \
            f"password '{macro.COMMON_PASSWD}';"
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertTrue(
            create_result.count(self.constant.CREATE_ROLE_SUCCESS_MSG) == 3,
            '执行失败:' + step_txt)

        step3_txt = '----step3: 线程1执行清理操作，expect: 清理失败----'
        self.log.info(step3_txt)
        sql_cmd1 = f'clean connection to all force ' \
                   f'to user {self.u_name1};'
        excute_cmd1 = f'-U {self.u_name1} -W "{macro.COMMON_PASSWD}" '
        session1 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd1, excute_cmd1, self.db_name))
        session1.setDaemon(True)
        session1.start()

        step4_txt = '----step4: 线程2执行清理操作，expect: 清理成功----'
        self.log.info(step4_txt)
        sql_cmd2 = f'clean connection to all force ' \
                   f'to user {self.u_name2};'
        excute_cmd2 = f'-U {self.u_name2} -W "{macro.COMMON_PASSWD}" '
        session2 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd2, excute_cmd2, self.db_name))
        session2.setDaemon(True)
        session2.start()

        step5_txt = '----step5: 线程3执行清理操作，expect: 清理成功----'
        self.log.info(step5_txt)
        sql_cmd3 = f'clean connection to all force ' \
                   f'to user {self.u_name3};'
        excute_cmd3 = f'-U {self.u_name3} -W "{macro.COMMON_PASSWD}" '
        session3 = ComThread(self.pri_sh.execut_db_sql,
                             args=(sql_cmd3, excute_cmd3, self.db_name))
        session3.setDaemon(True)
        session3.start()

        self.log.info("----session1执行结果----")
        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertIn(self.flag, session1_result, '执行失败:' + step3_txt)

        self.log.info("----session2执行结果----")
        session2.join()
        session2_result = session2.get_result()
        self.log.info(session2_result)
        self.assertIn(self.flag, session2_result, '执行失败:' + step4_txt)

        self.log.info("----session3执行结果----")
        session3.join()
        session3_result = session3.get_result()
        self.log.info(session3_result)
        self.assertIn(self.flag, session3_result, '执行失败:' + step5_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step6: 删除数据库和用户----'
        self.log.info(step_txt)
        drop_sql = f'drop database if exists {self.db_name};' \
                   f'drop user if exists {self.u_name1} cascade;' \
                   f'drop user if exists {self.u_name2} cascade;' \
                   f'drop user if exists {self.u_name3} cascade;'
        drop_result = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)

        self.log.info(f'-----{os.path.basename(__file__)} end-----')
